package cn.burnit.service;


import cn.burnit.pojo.Borrow;
import cn.burnit.pojo.ReaderBorrowBook;
import cn.burnit.utils.ConnectUtils;

import java.sql.*;

/**
 * 借阅jdbc方法
 */
public class BorrowService {

    PreparedStatement ps=null;
    Connection connection=null;
    ResultSet rs=null;

    /**
     * 通过书籍id和读者id连表查询
     * @param bookId
     * @param readerId
     * @return
     */
    public ReaderBorrowBook findBorrowBybookIdAndreaderId(String bookId, int readerId) {
        ConnectUtils.getDriver();
        try {
            connection=ConnectUtils.getConnection();
            String borrowSql="select b.bookname,b.author,b.publisher,b.publish_time,b.price,b.stock,b.outloan,r.id,r.readername,r.readertype,r.max_num,r.days_num FROM book b,reader r,bookandreader bw WHERE b.id=bw.book_id and r.id=bw.reader_id and b.id=? and r.id=?";
            ps=connection.prepareStatement(borrowSql);
            ps.setString(1,bookId);
            ps.setInt(2,readerId);
            rs = ps.executeQuery();
            while (rs.next()){
                ReaderBorrowBook readerBorrowBook = new ReaderBorrowBook();
                readerBorrowBook.setBookname(rs.getString(1));
                readerBorrowBook.setAuthor(rs.getString(2));
                readerBorrowBook.setPublisher(rs.getString(3));
                readerBorrowBook.setPublish_time(rs.getDate(4));
                readerBorrowBook.setPricce(rs.getFloat(5));
                readerBorrowBook.setStock(rs.getInt(6));
                readerBorrowBook.setOutloan(rs.getInt(7));
                readerBorrowBook.setReaderId(rs.getInt(8));
                readerBorrowBook.setReadername(rs.getString(9));
                readerBorrowBook.setReadertype(rs.getString(10));
                readerBorrowBook.setMax_num(rs.getInt(11));
                readerBorrowBook.setDays_num(rs.getInt(12));
                return readerBorrowBook;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }


    /**
     * 通过读者id查找借阅书籍
     * @param readerId
     * @return
     */
    public Borrow findBorrowById(int readerId) {
        ConnectUtils.getDriver();
        try {
            connection=ConnectUtils.getConnection();
            String sql="select * from borrow where reader_id=?";
            ps=connection.prepareStatement(sql);
            ps.setInt(1,readerId);
            rs=ps.executeQuery();
            while (rs.next()){
                Borrow borrow = new Borrow();
                borrow.setId(rs.getInt(1));
                borrow.setBookId(rs.getString(2));
                borrow.setReaderId(rs.getInt(3));
                borrow.setBorrowDate(rs.getDate(4));
                borrow.setBackDate(rs.getDate(5));
                borrow.setLatelyDate(rs.getDate(6));
                borrow.setIf_back(rs.getString(7));
                return borrow;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }


    public int addBorrowDate(Borrow borrowAdd) {
        ConnectUtils.getDriver();
        int i=0;
        try {
            connection=ConnectUtils.getConnection();
            String addDateSql="insert into borrow values(null,?,?,?,?,?,?)";
            ps=connection.prepareStatement(addDateSql);
            //ps.setInt(1,borrowAdd.getId());
            ps.setString(1,borrowAdd.getBookId());
            ps.setInt(2,borrowAdd.getReaderId());
            ps.setDate(3, (Date) borrowAdd.getBorrowDate());
            ps.setDate(4, (Date) borrowAdd.getBackDate());
            ps.setDate(5, (Date) borrowAdd.getLatelyDate());
            ps.setString(6,borrowAdd.getIf_back());
            i=ps.executeUpdate();
            if (i>0){
                System.out.println("添加成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }


    public int updateBackDate(Borrow bookIfbackReturn) {
        ConnectUtils.getDriver();
        int i = 0;
        try {
            connection = ConnectUtils.getConnection();
            String addDateSql = "update borrow set lately_date=? ,if_back=? where reader_id=?";
            ps = connection.prepareStatement(addDateSql);
            ps.setDate(1, (Date) bookIfbackReturn.getLatelyDate());
            ps.setString(2,bookIfbackReturn.getIf_back());
            ps.setInt(3,bookIfbackReturn.getReaderId());
            i = ps.executeUpdate();
            if (i > 0) {
                System.out.println("添加成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }
}
